package org.example.managere.service.feibiao.impl;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.example.managere.dto.feibiao.AmAapMotorKnDto;
import org.example.managere.entity.feibiao.AmAapMotorKn;
import org.example.managere.repository.feibiao.AmAapMotorKnRepository;
import org.example.managere.service.feibiao.AmAapMotorKnService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.hibernate.Hibernate;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * 电机知识表服务实现类
 */
@Service
@Slf4j
public class AmAapMotorKnServiceImpl implements AmAapMotorKnService {
    
    private final AmAapMotorKnRepository repository;
    private final JdbcTemplate jdbcTemplate;
    
    @Autowired
    public AmAapMotorKnServiceImpl(
            AmAapMotorKnRepository repository,
            @Qualifier("feibiaoDatasourceJdbcTemplate") JdbcTemplate jdbcTemplate) {
        this.repository = repository;
        this.jdbcTemplate = jdbcTemplate;
        log.info("AmAapMotorKnServiceImpl 初始化完成");
    }
    
    @Override
    @Transactional(readOnly = true)
    public Page<AmAapMotorKn> findAll(Pageable pageable) {
        log.info("查询所有电机知识记录，页码: {}, 每页数量: {}", pageable.getPageNumber(), pageable.getPageSize());
        try {
            Page<AmAapMotorKn> page = repository.findAll(pageable);
            
            // 检查LONGTEXT字段是否正确加载
            boolean allFieldsLoaded = true;
            for (AmAapMotorKn entity : page.getContent()) {
                if (entity.getHybridData() == null || entity.getHybridData().isEmpty() || 
                    entity.getLlmAnalysis() == null || entity.getLlmAnalysis().isEmpty()) {
                    allFieldsLoaded = false;
                    break;
                }
            }
            
            // 如果有LONGTEXT字段未正确加载，使用JDBC直接查询
            if (!allFieldsLoaded) {
                log.warn("检测到LONGTEXT字段未正确加载，尝试使用JDBC直接查询");
                return findAllWithJdbc(pageable);
            }
            
            return page;
        } catch (Exception e) {
            log.error("JPA查询失败，尝试使用JDBC直接查询: {}", e.getMessage(), e);
            return findAllWithJdbc(pageable);
        }
    }
    
    /**
     * 使用JDBC直接查询所有记录
     */
    private Page<AmAapMotorKn> findAllWithJdbc(Pageable pageable) {
        log.info("使用JDBC直接查询所有记录");
        String sql = "SELECT id, timestamp, hybrid_data, llm_analysis FROM am_aap_motorkn ORDER BY " + 
                getSortField(pageable) + " LIMIT ? OFFSET ?";
        
        String countSql = "SELECT COUNT(*) FROM am_aap_motorkn";
        
        try {
            // 查询记录总数
            Integer total = jdbcTemplate.queryForObject(countSql, Integer.class);
            if (total == null) {
                total = 0;
            }
            
            // 查询分页数据
            List<AmAapMotorKn> results = jdbcTemplate.query(
                sql,
                (rs, rowNum) -> mapResultSetToEntity(rs),
                pageable.getPageSize(),
                pageable.getOffset()
            );
            
            return new PageImpl<>(results, pageable, total);
        } catch (Exception e) {
            log.error("JDBC查询失败: {}", e.getMessage(), e);
            throw e;
        }
    }
    
    /**
     * 获取排序字段
     */
    private String getSortField(Pageable pageable) {
        String defaultSort = "timestamp DESC";
        if (pageable.getSort().isEmpty()) {
            return defaultSort;
        }
        
        StringBuilder sb = new StringBuilder();
        pageable.getSort().forEach(order -> {
            if (sb.length() > 0) {
                sb.append(", ");
            }
            sb.append(order.getProperty()).append(" ").append(order.getDirection());
        });
        
        return sb.length() > 0 ? sb.toString() : defaultSort;
    }
    
    /**
     * 将ResultSet映射为实体对象
     */
    private AmAapMotorKn mapResultSetToEntity(ResultSet rs) throws SQLException {
        AmAapMotorKn entity = new AmAapMotorKn();
        entity.setId(rs.getInt("id"));
        entity.setTimestamp(rs.getObject("timestamp", LocalDateTime.class));
        entity.setHybridData(rs.getString("hybrid_data"));
        entity.setLlmAnalysis(rs.getString("llm_analysis"));
        return entity;
    }
    
    @Override
    @Transactional(readOnly = true)
    public AmAapMotorKn findById(Integer id) {
        log.info("根据ID查询电机知识记录: {}", id);
        try {
            AmAapMotorKn entity = repository.findById(id)
                    .orElseThrow(() -> new RuntimeException("记录不存在"));
            
            // 检查LONGTEXT字段是否为空
            if (entity.getHybridData() == null || entity.getHybridData().isEmpty() || 
                entity.getLlmAnalysis() == null || entity.getLlmAnalysis().isEmpty()) {
                log.warn("LONGTEXT字段为空，尝试使用JDBC直接查询");
                return findByIdWithJdbc(id);
            }
            
            return entity;
        } catch (Exception e) {
            log.error("JPA查询失败，尝试使用JDBC直接查询: {}", e.getMessage(), e);
            return findByIdWithJdbc(id);
        }
    }
    
    /**
     * 使用JDBC直接根据ID查询记录
     */
    private AmAapMotorKn findByIdWithJdbc(Integer id) {
        log.info("使用JDBC直接根据ID查询记录: {}", id);
        String sql = "SELECT id, timestamp, hybrid_data, llm_analysis FROM am_aap_motorkn WHERE id = ?";
        
        try {
            List<AmAapMotorKn> results = jdbcTemplate.query(
                sql,
                (rs, rowNum) -> mapResultSetToEntity(rs),
                id
            );
            
            if (results.isEmpty()) {
                throw new RuntimeException("记录不存在");
            }
            
            return results.get(0);
        } catch (Exception e) {
            log.error("JDBC查询失败: {}", e.getMessage(), e);
            throw e;
        }
    }
    
    @Override
    @Transactional
    public AmAapMotorKn create(AmAapMotorKnDto dto) {
        log.info("创建新的电机知识记录");
        AmAapMotorKn entity = new AmAapMotorKn();
        BeanUtils.copyProperties(dto, entity);
        return repository.save(entity);
    }
    
    @Override
    @Transactional
    public AmAapMotorKn update(Integer id, AmAapMotorKnDto dto) {
        log.info("更新电机知识记录: {}", id);
        AmAapMotorKn entity = repository.findById(id)
                .orElseThrow(() -> new RuntimeException("记录不存在"));
        BeanUtils.copyProperties(dto, entity);
        return repository.save(entity);
    }
    
    @Override
    @Transactional
    public void delete(Integer id) {
        log.info("删除电机知识记录: {}", id);
        repository.deleteById(id);
    }
    
    @Override
    @Transactional(readOnly = true)
    public Page<AmAapMotorKn> searchByHybridData(String keyword, Pageable pageable) {
        log.info("搜索混合文本: {}", keyword);
        try {
            Page<AmAapMotorKn> page = repository.searchByHybridData(keyword, pageable);
            
            // 检查LONGTEXT字段是否正确加载
            boolean allFieldsLoaded = true;
            for (AmAapMotorKn entity : page.getContent()) {
                if (entity.getHybridData() == null || entity.getHybridData().isEmpty() || 
                    entity.getLlmAnalysis() == null || entity.getLlmAnalysis().isEmpty()) {
                    allFieldsLoaded = false;
                    break;
                }
            }
            
            // 如果有LONGTEXT字段未正确加载，使用JDBC直接查询
            if (!allFieldsLoaded) {
                log.warn("检测到LONGTEXT字段未正确加载，尝试使用备选方法");
                return searchByHybridDataWithLike(keyword, pageable);
            }
            
            return page;
        } catch (Exception e) {
            log.warn("全文索引搜索失败，使用备选方法: {}", e.getMessage());
            return searchByHybridDataWithLike(keyword, pageable);
        }
    }
    
    @Override
    @Transactional(readOnly = true)
    public Page<AmAapMotorKn> searchByLlmAnalysis(String keyword, Pageable pageable) {
        log.info("搜索分析结果: {}", keyword);
        try {
            Page<AmAapMotorKn> page = repository.searchByLlmAnalysis(keyword, pageable);
            
            // 检查LONGTEXT字段是否正确加载
            boolean allFieldsLoaded = true;
            for (AmAapMotorKn entity : page.getContent()) {
                if (entity.getHybridData() == null || entity.getHybridData().isEmpty() || 
                    entity.getLlmAnalysis() == null || entity.getLlmAnalysis().isEmpty()) {
                    allFieldsLoaded = false;
                    break;
                }
            }
            
            // 如果有LONGTEXT字段未正确加载，使用JDBC直接查询
            if (!allFieldsLoaded) {
                log.warn("检测到LONGTEXT字段未正确加载，尝试使用备选方法");
                return searchByLlmAnalysisWithLike(keyword, pageable);
            }
            
            return page;
        } catch (Exception e) {
            log.warn("全文索引搜索失败，使用备选方法: {}", e.getMessage());
            return searchByLlmAnalysisWithLike(keyword, pageable);
        }
    }
    
    @Override
    @Transactional(readOnly = true)
    public Page<AmAapMotorKn> searchByHybridDataWithLike(String keyword, Pageable pageable) {
        log.info("使用LIKE搜索混合文本: {}", keyword);
        try {
            Page<AmAapMotorKn> page = repository.searchByHybridDataWithLike(keyword, pageable);
            
            // 检查LONGTEXT字段是否正确加载
            boolean allFieldsLoaded = true;
            for (AmAapMotorKn entity : page.getContent()) {
                if (entity.getHybridData() == null || entity.getHybridData().isEmpty() || 
                    entity.getLlmAnalysis() == null || entity.getLlmAnalysis().isEmpty()) {
                    allFieldsLoaded = false;
                    break;
                }
            }
            
            // 如果有LONGTEXT字段未正确加载，使用JDBC直接查询
            if (!allFieldsLoaded) {
                log.warn("检测到LONGTEXT字段未正确加载，尝试使用JDBC直接查询");
                return searchByHybridDataWithJdbc(keyword, pageable);
            }
            
            return page;
        } catch (Exception e) {
            log.error("JPA搜索失败，尝试使用JDBC直接查询: {}", e.getMessage(), e);
            return searchByHybridDataWithJdbc(keyword, pageable);
        }
    }
    
    /**
     * 使用JDBC直接搜索混合文本
     */
    private Page<AmAapMotorKn> searchByHybridDataWithJdbc(String keyword, Pageable pageable) {
        log.info("使用JDBC直接搜索混合文本: {}", keyword);
        String sql = "SELECT id, timestamp, hybrid_data, llm_analysis FROM am_aap_motorkn " +
                     "WHERE hybrid_data LIKE ? ORDER BY " + getSortField(pageable) + " LIMIT ? OFFSET ?";
        
        String countSql = "SELECT COUNT(*) FROM am_aap_motorkn WHERE hybrid_data LIKE ?";
        
        try {
            String likePattern = "%" + keyword + "%";
            
            // 查询记录总数
            Integer total = jdbcTemplate.queryForObject(countSql, Integer.class, likePattern);
            if (total == null) {
                total = 0;
            }
            
            // 查询分页数据
            List<AmAapMotorKn> results = jdbcTemplate.query(
                sql,
                (rs, rowNum) -> mapResultSetToEntity(rs),
                likePattern,
                pageable.getPageSize(),
                pageable.getOffset()
            );
            
            return new PageImpl<>(results, pageable, total);
        } catch (Exception e) {
            log.error("JDBC搜索失败: {}", e.getMessage(), e);
            throw e;
        }
    }
    
    @Override
    @Transactional(readOnly = true)
    public Page<AmAapMotorKn> searchByLlmAnalysisWithLike(String keyword, Pageable pageable) {
        log.info("使用LIKE搜索分析结果: {}", keyword);
        try {
            Page<AmAapMotorKn> page = repository.searchByLlmAnalysisWithLike(keyword, pageable);
            
            // 检查LONGTEXT字段是否正确加载
            boolean allFieldsLoaded = true;
            for (AmAapMotorKn entity : page.getContent()) {
                if (entity.getHybridData() == null || entity.getHybridData().isEmpty() || 
                    entity.getLlmAnalysis() == null || entity.getLlmAnalysis().isEmpty()) {
                    allFieldsLoaded = false;
                    break;
                }
            }
            
            // 如果有LONGTEXT字段未正确加载，使用JDBC直接查询
            if (!allFieldsLoaded) {
                log.warn("检测到LONGTEXT字段未正确加载，尝试使用JDBC直接查询");
                return searchByLlmAnalysisWithJdbc(keyword, pageable);
            }
            
            return page;
        } catch (Exception e) {
            log.error("JPA搜索失败，尝试使用JDBC直接查询: {}", e.getMessage(), e);
            return searchByLlmAnalysisWithJdbc(keyword, pageable);
        }
    }
    
    /**
     * 使用JDBC直接搜索分析结果
     */
    private Page<AmAapMotorKn> searchByLlmAnalysisWithJdbc(String keyword, Pageable pageable) {
        log.info("使用JDBC直接搜索分析结果: {}", keyword);
        String sql = "SELECT id, timestamp, hybrid_data, llm_analysis FROM am_aap_motorkn " +
                     "WHERE llm_analysis LIKE ? ORDER BY " + getSortField(pageable) + " LIMIT ? OFFSET ?";
        
        String countSql = "SELECT COUNT(*) FROM am_aap_motorkn WHERE llm_analysis LIKE ?";
        
        try {
            String likePattern = "%" + keyword + "%";
            
            // 查询记录总数
            Integer total = jdbcTemplate.queryForObject(countSql, Integer.class, likePattern);
            if (total == null) {
                total = 0;
            }
            
            // 查询分页数据
            List<AmAapMotorKn> results = jdbcTemplate.query(
                sql,
                (rs, rowNum) -> mapResultSetToEntity(rs),
                likePattern,
                pageable.getPageSize(),
                pageable.getOffset()
            );
            
            return new PageImpl<>(results, pageable, total);
        } catch (Exception e) {
            log.error("JDBC搜索失败: {}", e.getMessage(), e);
            throw e;
        }
    }
    
    /**
     * 确保加载LONGTEXT字段
     */
    private void initializeLobFields(AmAapMotorKn entity) {
        if (entity != null) {
            // 强制加载LONGTEXT字段
            log.debug("初始化LONGTEXT字段: hybridData长度={}, llmAnalysis长度={}", 
                    entity.getHybridData() != null ? entity.getHybridData().length() : 0,
                    entity.getLlmAnalysis() != null ? entity.getLlmAnalysis().length() : 0);
            
            // 如果字段为空但应该有内容，则重新加载
            if ((entity.getHybridData() == null || entity.getHybridData().isEmpty() || 
                entity.getLlmAnalysis() == null || entity.getLlmAnalysis().isEmpty()) && entity.getId() != null) {
                log.warn("发现空LONGTEXT字段，尝试重新加载实体: {}", entity.getId());
                try {
                    AmAapMotorKn refreshed = findByIdWithJdbc(entity.getId());
                    entity.setHybridData(refreshed.getHybridData());
                    entity.setLlmAnalysis(refreshed.getLlmAnalysis());
                } catch (Exception e) {
                    log.error("重新加载实体失败: {}", e.getMessage(), e);
                }
            }
        }
    }
    
    /**
     * 实体转DTO（用于兼容旧代码）
     */
    private AmAapMotorKnDto convertToDto(AmAapMotorKn entity) {
        AmAapMotorKnDto dto = new AmAapMotorKnDto();
        BeanUtils.copyProperties(entity, dto);
        return dto;
    }
} 